package edu.tamu.cse.teamproject1.AirlineApplication;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.io.*;

import edu.tamu.cse.teamproject1.Database.ColumnDefinition;
import edu.tamu.cse.teamproject1.Database.Database;
import edu.tamu.cse.teamproject1.Database.EnumColumnType;
import edu.tamu.cse.teamproject1.Database.Table;
import edu.tamu.cse.teamproject1.Database.Tuple;
import edu.tamu.cse.teamproject1.Database.TupleElement;
import edu.tamu.cse.teamproject1.Database.Exceptions.SqlException;
import edu.tamu.cse.teamproject1.Database.Interfaces.DatabaseAPI;
import edu.tamu.cse.teamproject1.Database.Interfaces.IColumnDefinition;
import edu.tamu.cse.teamproject1.Database.Interfaces.IDatabaseTable;
import edu.tamu.cse.teamproject1.Database.Interfaces.ITuple;
import edu.tamu.cse.teamproject1.Database.Interfaces.ITupleElement;
import edu.tamu.cse.teamproject1.Database.Interfaces.IXMLParser;
import edu.tamu.cse.teamproject1.Database.Persistence.XMLParser;

/**
 * @author Ryan McAuley
 * This class is basically an application testing the correctness and
 * consistency of our database. This is something that a typical client
 * would use when implementing our API.
 */

public class testApplication {
	DatabaseAPI airplaneApplication;
	
	testApplication() throws IOException{

		airplaneApplication = getXMLDatabase();
	}
	
	public static void main(String args[]) throws IOException{
		testApplication test = new testApplication();
		
		
		/*Prep Level
		 * Will eventually use the XML files, but until that is catered to the new
		 * qualifications, we'll have to input it the old fashioned way.
		 */
	
		
		/*Display Welcome Message*/
		test.welcomeGraphic();
	
		
		/*Query Level*/
		int menuOption = -1;
		String userInput = new String();
		int flightNumber = 0;
		String flightInput = new String();
		String destination= new String();
		String userDate = new String();
		Date today = new Date();
		int terminal = 0;
		String terminalInput = new String();
		int firstClassFlightNumber;
		String firstClassInput = new String();
		int businessClassFlightNumber;
		String businessClassInput = new String();
		
		test.displayMenu();
		
		List<IDatabaseTable> tableList = test.airplaneApplication.getTableList();
		//System.out.println(tableList.size());
		IDatabaseTable flightTable = tableList.get(0);
		//System.out.println(flightTable.getName());
		//System.out.println(flightTable.getColumnLists().size());
		//System.out.println(flightTable.getAllTuples().size());
		
		
		while(menuOption != 0){
			
			//the table that will display the view created by the SQL statements 
			IDatabaseTable tableView = null;
			
			System.out.println("Please Enter Your Menu Selection: ");
			BufferedReader br = new BufferedReader(new InputStreamReader(System.in) );
			try {
			userInput = br.readLine();
			}	catch (IOException e) {
					e.printStackTrace();
				}
			menuOption = Integer.parseInt(userInput);
			
			switch(menuOption){
				//Exit Command
				case 0:
					System.out.println("Thank you! Have a nice day!");
					return;
				//List All Flights
				case 1:
					tableView = test.listAllFlights();
					break;
				//List All Passengers
				case 2:
					tableView = test.listAllPassengers();
					break;
				//List Ticket Information
				case 3:
					tableView = test.listAllTicketInfo();
					break;
				//List Passengers by Flight
				case 4:
					System.out.println("Please state the flight number: ");
					try {
						flightInput = br.readLine();
					} catch (IOException e1) {
						e1.printStackTrace();
					}
					flightNumber = Integer.parseInt(flightInput);
					tableView = test.listPassengersByFlight(flightNumber);
					break;
				//List Availability by Destination
				case 5:
					System.out.println("Please state the destination: ");
					try {
						destination = br.readLine();
					} catch (IOException e) {
						e.printStackTrace();
					}
					tableView = test.listAvailabilityByDestination(destination);
					break;
				//List Flights by Date
				case 6:
					DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
					System.out.println("Please state the flight's date in the form mm/dd/yyyy: ");
					try {
						userDate = br.readLine();
					}	catch (IOException e) {
						e.printStackTrace();
					}
					try
				        {
				          today = df.parse(userDate);            
				        } catch (ParseException e)
				        {
				            e.printStackTrace();
				        }
			        tableView = test.listFlightsByDate(today);
					break;
				//List Flights by Terminal
				case 7:
					System.out.println("Please input the terminal number: ");
					try {
						terminalInput = br.readLine();
					} catch (IOException e) {
						e.printStackTrace();
					}
					terminal = Integer.parseInt(terminalInput);
					tableView = test.listFlightsByTerminal(terminal);
					break;
				//List Flights With Delays
				case 8:
					tableView = test.listFlightsWithDelays();
					break;
				//List Flights Without Delays
				case 9:
					tableView = test.listFlightsWithoutDelays();
					break;
				//List First Class Members By Flight
				case 10:
					System.out.println("Please input the flight number: ");
					try {
						firstClassInput = br.readLine();
					} catch (IOException e) {
						e.printStackTrace();
					}
					firstClassFlightNumber = Integer.parseInt(firstClassInput);
					tableView = test.listFirstClassMembersByFlight(firstClassFlightNumber);
					break;
				//List Business Class Members By Flight
				case 11:
					System.out.println("Please input the flight number: ");
					try {
						businessClassInput = br.readLine();
					} catch (IOException e) {
						e.printStackTrace();
					}
					businessClassFlightNumber = Integer.parseInt(businessClassInput);
					tableView = test.listBusinessClassMembersByFlight(businessClassFlightNumber);
					break;
				//List All Flights Passenger Is On
				case 12:
					tableView = test.listTotalFlights();
					break;
				/*tests the test table*/
				case 13:
					test.viewTestTable();
					break;
				//Erroneous Menu Input
				default:
					System.out.println("Invalid Menu Command!");
					break;	
			}
			test.viewTable(tableView);
		}
	}
	
	DatabaseAPI getXMLDatabase() throws IOException{
		IXMLParser parseXML = new XMLParser();
		parseXML.createTableStructure("applicationTables.xml");
		parseXML.fillTable("applicationData.xml");
		return parseXML.getGeneratedDatabase();
	}
	
	
	
	/**
	 *
	 *Prints the menu to the console
	 *@param void
	 *@return void
	 *
	 */
	void displayMenu(){
		System.out.println("Menu Options:");
		System.out.println("0: Exit Application");
		System.out.println("1: List All Flights");
		System.out.println("2: List All Passengers"); //First and Last Name
		System.out.println("3: List All Tickets"); //ID and Passenger First and Last Name
		System.out.println("4: List All Passengers By Flight");
		System.out.println("5: List Flight Availability by Destination");
		System.out.println("6: List All Flights By Date");
		System.out.println("7: List All Flights By Terminal");
		System.out.println("8: View Flights With Delays");
		System.out.println("9: View Flights that are Ontime");
		System.out.println("10: View First Class Members By Flight");
		System.out.println("11: View Buisness Class Members By Flight");
		System.out.println("12: List Total Number of Flights");
		System.out.println("13: View Test Table");
	}

	/**
	 *
	 *Lists all flights in a view
	 *@param void
	 *@return void
	 *
	 */
	IDatabaseTable listAllFlights(){
		IDatabaseTable tableView;
		try {
			
			tableView = airplaneApplication.select("SELECT * FROM FlightInfo");
			//System.out.println(tableView.getName());
			//System.out.println(tableView.getColumnLists().size());
			//System.out.println(tableView.getAllTuples().size());
			return tableView;
			
		} 	catch (SqlException e) {
				e.printStackTrace();
			}
		
		return null;
	
	}

	/**
	 *
	 *Lists all passengers in a view
	 *@returns void
	 *
	 */
	IDatabaseTable listAllPassengers(){
		IDatabaseTable tableView;

		
		try {
			tableView = airplaneApplication.select("SELECT * FROM PassengerInfo");
			return tableView;
			
		} 	catch (SqlException e) {
				e.printStackTrace();
			}
		
		return null;
	}

	/**
	 *
	 *Lists all ticket information in a view
	 *@return void
	 *
	 */
	IDatabaseTable listAllTicketInfo(){
		IDatabaseTable tableView;
		
		try {
			tableView = airplaneApplication.select("SELECT * FROM TicketInfo");
			return tableView;
		} 	catch (SqlException e) {
				e.printStackTrace();
			}
		
		return null;
	}

	/**
	 *
	 *Lists all passengers by their flight number in a view
	 *@param int flightNumber
	 *@return void
	 *
	 */
	IDatabaseTable listPassengersByFlight(int flightNumber){
		IDatabaseTable tableView;
		
		try {
			tableView = airplaneApplication.select("SELECT TicketInfo.PassengerID, " +
						"PassengerInfo.LastName, " +
						"PassengerInfo.FirstName " +
						"FROM TicketInfo, PassengerInfo WHERE " +
						"TicketInfo.PassengerID = PassengerInfo.PassengerID AND " +
						"TicketInfo.PassengerID = "+ Integer.toString(flightNumber));
			
			return tableView;
			
		} 	catch (SqlException e) {
				e.printStackTrace();
			}
		
		return null;
	}

	/**
	 *
	 *Lists availability of seats on flights going to a particular destination in a view
	 *@param String destination
	 *@return void
	 *
	 */
	IDatabaseTable listAvailabilityByDestination(String destination){
		IDatabaseTable tableView;

		try {
			tableView = airplaneApplication.select("SELECT FlightInfo.PlaneID, " +
						"FROM FlightInfo WHERE " +
						"FlightInfo.Destination = "+ destination);
			return tableView;
			
		} 	catch (SqlException e) {
				e.printStackTrace();
			}
		return null;
	}

	/**
	 *
	 *Lists all flights by a particular date in a view
	 *@param Date specificDate
	 *@return void
	 *
	 */
	IDatabaseTable listFlightsByDate(Date specificDate){
		IDatabaseTable tableView;

		SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
		String convertedSpecificDate = formatter.format(specificDate);
		try {
			tableView = airplaneApplication.select("SELECT FlightInfo.PlaneID, " +
						"FROM FlightInfo WHERE " +
						"FlightInfo.Date = "+ convertedSpecificDate);
			return tableView;
			
		} 	catch (SqlException e) {
				e.printStackTrace();
			}
		
		return null;
	}

	/**
	 *
	 *Lists all flights by terminal in a view
	 *@param int specificTerminal
	 *@return void
	 *
	 */
	IDatabaseTable listFlightsByTerminal(int specificTerminal){
		IDatabaseTable tableView;

		try {
			tableView = airplaneApplication.select("SELECT FlightInfo.PlaneID, " +
						"FROM FlightInfo WHERE " +
						"FlightInfo.TerminalNumber = "+ Integer.toString(specificTerminal));
			
			return tableView;
			
		} 	catch (SqlException e) {
				e.printStackTrace();
			}
		
		return null;
	}

	/**
	 *
	 *Displays all of the flights that are currently experiencing delays in a view
	 *@return void
	 *
	 */
	IDatabaseTable listFlightsWithDelays(){
		IDatabaseTable tableView;

		try {
			tableView = airplaneApplication.select("SELECT FlightInfo.PlaneID, " +
						"FROM FlightInfo WHERE " +
						"FlightInfo.DesinationTimeInMin > 0");
			return tableView;
		} 	catch (SqlException e) {
				e.printStackTrace();
			}
		return null;
	}

	/**
	 *
	 *Lists all flights that are not experiencing delays in a view
	 *@return void
	 *
	 */
	IDatabaseTable listFlightsWithoutDelays(){
		IDatabaseTable tableView;

		try {
			tableView = airplaneApplication.select("SELECT FlightInfo.PlaneID, " +
						"FROM FlightInfo WHERE " +
						"FlightInfo.DesinationTimeInMin = 0");
			return tableView;
		} 	catch (SqlException e) {
				e.printStackTrace();
			}
		return null;
	}

	/**
	 *
	 *Lists members of a particular flight who are in first class in a view
	 *@param int flightNumber
	 *@return void
	 *
	 */
	IDatabaseTable listFirstClassMembersByFlight(int flightNumber){
		IDatabaseTable tableView;

		try {
			tableView = airplaneApplication.select("SELECT TicketInfo.PassengerID, " +
						"PassengerInfo.PassengerID, PassengerInfo.LastName, " +
						"PassengerInfo.FirstName " +
						"FROM TicketInfo, PassengerInfo WHERE " +
						"PassengerInfo.FirstClass = Yes");
			return tableView;
			
		} 	catch (SqlException e) {
				e.printStackTrace();
			}
		return null;
	}

	/**
	 *
	 *Lists members of a particular flight who are flying business class in a view
	 *@param int flightNumber
	 *@return void
	 *
	 */
	IDatabaseTable listBusinessClassMembersByFlight(int flightNumber){
		IDatabaseTable tableView;

		try {
			tableView = airplaneApplication.select("SELECT TicketInfo.PassengerID, " +
						"PassengerInfo.PassengerID, PassengerInfo.LastName, " +
						"PassengerInfo.FirstName " +
						"FROM TicketInfo, PassengerInfo WHERE " +
						"PassengerInfo.FirstClass = No");
			
			return tableView;
		} 	catch (SqlException e) {
				e.printStackTrace();
			}
		return null;
	}

	/**
	 *
	 *Lists total number of flights at the airport
	 *@return void
	 *
	 */
	IDatabaseTable listTotalFlights(){
		IDatabaseTable tableView;

		try {
			tableView = airplaneApplication.select("SELECT COUNT(FlightNumber), " +
						"FROM FlightInfo");
			return tableView;
		} 	catch (SqlException e) {
				e.printStackTrace();
			}
		return null;
	}
	
	/**
	 * this opens a view in the GUI to display the test table
	 */
	void viewTestTable(){
		try {
			IDatabaseTable testTable = createTestTable();
			String[] testColumnNames = testTable.getTableColumnNames();
			Object[][] test2DArray = testTable.getTableTuples();
			tableView viewTable = new tableView(testColumnNames, test2DArray);
			viewTable.createAndShowGUI();
			
		} catch (SqlException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	/**
	 * this opens a table view in the GUI and displays its members
	 * @param tableView
	 */
	void viewTable(IDatabaseTable tableView){
//		System.out.println(tableView.getName());
//		System.out.println(tableView.getColumnLists().size());
//		System.out.println(tableView.getAllTuples().size());

		String[] columnNames = tableView.getTableColumnNames();
		Object[][] tuple2DArray = tableView.getTableTuples();
		tableView viewTable = new tableView(columnNames, tuple2DArray);
		viewTable.createAndShowGUI();
	}
	
	/**
	 * this creates a test table to be printed to screen
	 * @return the database as a DatabaseAPI
	 * @throws SqlException 
	 */
	IDatabaseTable createTestTable() throws SqlException{
		/*create a list of IColumnDefitions for the test table*/
		ArrayList<IColumnDefinition> testColumns = new ArrayList<IColumnDefinition>();
		
		/*first column*/
		IColumnDefinition testColumn1 = new ColumnDefinition(EnumColumnType.integer, "ColumnInt");
		/*second column*/
		IColumnDefinition testColumn2 = new ColumnDefinition(EnumColumnType.floating, "ColumnFloat");
		/*third column*/
		IColumnDefinition testColumn3 = new ColumnDefinition(EnumColumnType.varchar, "ColumnVarChar");
		
		/*add columns to list*/
		testColumns.add(testColumn1);
		testColumns.add(testColumn2);
		testColumns.add(testColumn3);
		
		/*create test table*/
		IDatabaseTable testTable = new Table("Flight_Info",testColumns);
		
		/*create four tupleElements*/
		ITupleElement testTupleElement = new TupleElement();
		ITupleElement tupleElem1 = testTupleElement.createTupleElement(10, "ColumnInt");
		ITupleElement tupleElem2 = testTupleElement.createTupleElement(10.5F, "ColumnFloat");		
		ITupleElement tupleElem3 = testTupleElement.createTupleElement("Howdy", "ColumnVarChar");
		ITupleElement tupleElem4 = testTupleElement.createTupleElement(15, "ColumnInt");
		ITupleElement tupleElem5 = testTupleElement.createTupleElement(9.5F, "ColumnFloat");
		ITupleElement tupleElem6 = testTupleElement.createTupleElement("Gig 'Em", "ColumnVarChar");
		
		
		/*create a two ITuples for the test table*/
		ITuple testTuple1 = new Tuple();
		testTuple1.addTupleElement(tupleElem1);
		testTuple1.addTupleElement(tupleElem2);
		testTuple1.addTupleElement(tupleElem3);
		
		ITuple testTuple2 = new Tuple();
		testTuple2.addTupleElement(tupleElem4);
		testTuple2.addTupleElement(tupleElem5);
		testTuple2.addTupleElement(tupleElem6);

		
		testTable.addTuple(testTuple1);
		testTable.addTuple(testTuple2);

		
		return testTable;
	}
	
	/**
	 * Prints the welcome airplane
	 */
	
	void welcomeGraphic(){
		System.out.println("**************************");
		System.out.println("       *           $$$$$$");
		System.out.println(" *    @**         $$$$$$$$ ");
		System.out.println(" **   @***         $$$$$$ ");
		System.out.println(" ***   ****");
		System.out.println("*****************");
		System.out.println(" *******************");
		System.out.println(" *********************");
		System.out.println(" ***   ****");
		System.out.println(" **   @*** #################");
		System.out.println(" *    @**  !     HOWDY!    !");
		System.out.println("       *   #################");
		System.out.println("\nThe \"Plane Jane\" Database System");
		System.out.println("Creators: Ben Unsworth, Christopher Weldon, Ryan McAuley");
		System.out.println("Version 1.0");
		System.out.println("********************************************************\n");
	}
}
